package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class BDMaterial implements IFDBMaterial
{
	 private  Connection connection;
	
	 public BDMaterial() 
	    {
	      connection = DbConnection.getInstance().getDBcon();
	    }
	 
	 public ArrayList<Material> getAllMaterials() 
	 	{
	      return miscWhere("");
	    }
	 
	 private ArrayList<Material> miscWhere(String wClause)
		{
			ArrayList<Material> materials = new ArrayList<>();
			
			ResultSet results;
			String query = buildQuery(wClause);
			
			try{
				Statement stmt = connection.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				
				while(results.next())
				{
					Material material = buildMaterial(results);
					materials.add(material);
				}
				
				stmt.close();
				
			}
			catch(Exception ex){}
			return materials;
		  
	    }
	    
	 
	 private String buildQuery(String wClause)
		{
		    String query="SELECT materialId, name, type FROM Materials";
			
			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;
				
			return query;
		}
		
	 private Material buildMaterial(ResultSet results){
	        Material materialObj = new Material();
	          try{ 
	                materialObj.setMaterialId(results.getInt("materialId"));
	                materialObj.setMaterialName(results.getString("name"));
	                materialObj.setMaterialType(results.getString("type"));
	          }
	         catch(Exception e)
	         {
	             System.out.println("Cannot create material object.");
	         }
	         return materialObj;
	      
	    }
	 
	 public int updateMaterial(Material material)
		{
			Material materialObj  = material;
			int rc=-1;

			String query="UPDATE Materials SET "+
			 	  "materialId ="+ materialObj.getMaterialId()+", "+
			 	  "name ='"+ materialObj.getMaterialName() + "', " +
			      "type ='"+ materialObj.getMaterialType() + "' " +
			      " WHERE materialId = "+ materialObj.getMaterialId() + "";
	                System.out.println("Update query:" + query);
	  		try{ 
		 		Statement stmt = connection.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	rc = stmt.executeUpdate(query);

		 	 	stmt.close();
			}
		 	catch(Exception ex){
		 	 	System.out.println("Update exception in materials db: "+ex);
		  	}
			return(rc);
		}
	 
	 public int deleteMaterial(int materialId)
		{
	               int rc=-1;
		  
		  	String query="DELETE FROM Materials WHERE materialId = " +materialId;
	                System.out.println(query);
		  	try{ 
		 		Statement stmt = connection.createStatement();
		 		stmt.setQueryTimeout(5);
		 	  	rc = stmt.executeUpdate(query);
		 	  	stmt.close();
	  		}
	   	        catch(Exception ex){
		 	  	System.out.println("Delete exception in Materials db: "+ex);
	   	        }
			return(rc);
		}

	 public int insertMaterial(Material material) throws Exception
	    {   
	        
	  
	       int rc = -1;
		   String query="INSERT INTO Materials(materialId, name, type)  VALUES "
				+ "("+material.getMaterialId()+",'"+material.getMaterialName()+"','"+material.getMaterialType()+"')";
	                     

	       System.out.println("insert : " + query);
	      try{ 
	          Statement stmt = connection.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	      }
	       catch(SQLException ex){
	          System.out.println("Error inserting material");
	          throw new Exception ("Error");
	       }
	       return(rc);
	    }

	 public Material findMaterial(int materialId)
	    {   String wClause = "  materialId = " + materialId;
	        return singleWhere(wClause);
	    }
	 
	 private Material singleWhere(String wClause)
		{
			Material material = new Material();
			
			ResultSet results;
			String query = buildQuery(wClause);
			
			
			try{
				Statement stmt = connection.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				if(results.next())
					material = buildMaterial(results);
				
				stmt.close();
			}
			catch(Exception ex){}
			
			return material;
		}

	
	 
}
